package pc.sql;

import java.util.*;
import java.sql.*;
import pc.sql.value.*;
import pc.system.*;

public class SQLCommand
{
  private Actions owner = null;
  private Connection conn = null;
  private ConnectionPool    connPool   = null;
  private Vector values = null;
  private String sql = null;
  private  boolean  sorted = false;
  private String sorted_table = "";
  private String sorted_field = "";
  private String limit = null;

  public SQLCommand(ConnectionPool connPool)
  {
    this.connPool = connPool;
    if (connPool != null) conn = connPool.getConnection();
  }

  public SQLCommand(String hostName,String dbName, String user, String password)
  {
    this.connPool = new ConnectionPool(hostName,dbName,user,password);
    if (connPool != null) conn = connPool.getConnection();
  }

  public SQLCommand(Actions owner)
  {
    this(owner.getConnectionPool());
    this.owner = owner;
  }

  public void setOwner(Actions owner)
  {
    this.owner = owner;
  }

  protected void finalize()
  {
  }

  public void returnConnection()
  {
    if (connPool != null) connPool.returnConnection(conn);
  }

  public void closeConnection()
  {
    if (connPool != null) {
      connPool.returnConnection(conn);
      connPool.releaseConnection(conn);
    }
  }

  public Connection getConnection()
  {
    return this.conn;
  }

  public ConnectionPool getConnectionPool()
  {
    return this.connPool;
  }

  public void setSqlValue(String sql)
  {
    this.sql = sql;
  }

  public void setValues(Vector values)
  {
    this.values = values;
  }

  public void setSort(String sorted_table)
  {
    this.sorted = false;
    if (owner == null) return;
    this.sorted_field = owner.getRequest().getStringValue("sorted_field");
    this.sorted_table = owner.getRequest().getStringValue("sorted_table");
    if (this.sorted_field.length() == 0) return;
    this.sorted = this.sorted_table.equals(sorted_table);
  }

  public void setLimit(String limit)
  {
    this.limit = limit;
  }

  private String getLimit()
  {
    String res = limit != null ? " " + limit : " ";
    limit = null;
    return res;
  }

  private String removeBlank(String s)
  {
    StringBuffer sb = new StringBuffer(s);
    int i = sb.length()-1;
    //int found = 0;
    while (i >= 0) {
      if (sb.charAt(i) == ' ') {
        i--;
        while (i >=0 && sb.charAt(i) == ' ') sb.deleteCharAt(i--);
      } else i--;
    }
    return sb.toString();
  }

  private String setSortClause(String sql)
  {
    String s = removeBlank(sql);
    int i = s.toLowerCase().lastIndexOf("order by");
    if (i > 0) s = s.substring(0,i);
    s += (" ORDER BY " + sorted_field);
    Boolean dir = (Boolean)owner.getSortFields().get(sorted_table + "." + sorted_field);
    if (dir == null) dir = Boolean.FALSE;
    dir = (dir == Boolean.TRUE) ? Boolean.FALSE : Boolean.TRUE;
    owner.getSortFields().put(sorted_table + "." + sorted_field, dir);

    if (dir == Boolean.TRUE) s += " ASC ";
    else s += " DESC ";

    return s;
  }

  public ResultSet executeQuery()
  {
    if (conn == null) return null;

    if (sorted)  sql = setSortClause(sql);

    sorted = false;

    ResultSet         rs        = null;
    Statement         stm       = null;
    PreparedStatement pstm      = null;

    try {
      if (values != null && values.size() > 0) {
        pstm = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        setValues(pstm, values);
        rs = pstm.executeQuery();
      } else {
        stm = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);//ResultSet.CONCUR_UPDATABLE);
        rs = stm.executeQuery(sql);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return rs;
  }

  public ResultSet executeQuery(String sql, Vector values)
  {
    setSqlValue(sql);
    setValues(values);
    return executeQuery();
  }

  public ResultSet executeQuery(String sql)
  {
    return executeQuery(sql,null);
  }

  public int executeUpdate(boolean forced)
  {
    if (!forced && owner.new_version) {
      if (owner.getPermission() != 3) return 0;
    }

    if (conn == null) return 0;

    int noOfRows = 0;
    PreparedStatement pstmt = null;
    Statement stmt = null;
    
    try {
      if (values != null && values.size() > 0) {
        pstmt = conn.prepareStatement(sql);
        setValues(pstmt, values);
        noOfRows = pstmt.executeUpdate();
      } else {
        stmt = conn.createStatement();
       
        noOfRows = stmt.executeUpdate(sql);
      }
    }   catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      try {
        if (stmt != null)  stmt.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    return noOfRows;
  }

  public int executeUpdate()
  {
    return executeUpdate(false);
  }

  public int executeUpdate(String sql, Vector values, boolean forced)
  {
    setSqlValue(sql);
    setValues(values);
    return executeUpdate(forced);
  }

  public int executeUpdate(String sql, Vector values)
  {
    return executeUpdate(sql, values, false);
  }

  public int executeUpdate(String sql, boolean forced)
  {
    return executeUpdate(sql,null, forced);
  }

  public int executeUpdate(String sql)
  {
    return executeUpdate(sql,false);
  }

  private void setValues(PreparedStatement pstmt, Vector values) throws SQLException
  {
    for (int i = 0; i < values.size(); i++) {
      try {
        Value v = (Value) values.elementAt(i);
        if (v instanceof BigDecimalValue) {
          pstmt.setBigDecimal(i + 1, v.getBigDecimal());
        }
        else if (v instanceof BooleanValue) {
          pstmt.setBoolean(i + 1, v.getBoolean());
        }
        else if (v instanceof ByteValue) {
          pstmt.setByte(i + 1, v.getByte());
        }
        else if (v instanceof BytesValue) {
          pstmt.setBytes(i + 1, v.getBytes());
        }
        else if (v instanceof DateValue) {
          pstmt.setDate(i + 1, v.getDate());
        }
        else if (v instanceof DoubleValue) {
          pstmt.setDouble(i + 1, v.getDouble());
        }
        else if (v instanceof FloatValue) {
          pstmt.setFloat(i + 1, v.getFloat());
        }
        else if (v instanceof IntValue) {
          pstmt.setInt(i + 1, v.getInt());
        }
        else if (v instanceof LongValue) {
          pstmt.setLong(i + 1, v.getLong());
        }
        else if (v instanceof ShortValue) {
          pstmt.setShort(i + 1, v.getShort());
        }
        else if (v instanceof StringValue) {
          pstmt.setString(i + 1, v.getString());
        }
        else if (v instanceof TimeValue) {
          pstmt.setTime(i + 1, v.getTime());
        }
        else if (v instanceof TimestampValue) {
          pstmt.setTimestamp(i + 1, v.getTimestamp());
        }
        else {
          pstmt.setObject(i + 1, v.getObject());
        }
      }
      catch (UnsupportedConversionException e) {
        e.printStackTrace();
        // Can not happen here since we test the type first
      }
    }
  }

  public void close(ResultSet rs)
  {
    if (rs == null) return;
    try {
      Statement stm = rs.getStatement();
      rs.close();
      if (stm != null) stm.close();
    } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    public int dropTable(String table)
    {
      if (table != null) {
        String sql = "DROP table IF EXISTS " + table;
        return this.executeUpdate(sql,true);
      } else return 0;
    }

    public String createTableTemp(String table,String sql, Vector vp)
    {
      dropTable(table);
      sql = "CREATE TEMPORARY TABLE " + table + " " + sql;
      this.executeUpdate(sql,vp,true);
      return table;
    }

    public String createTableTemp(String table,String sql)
    {
      return createTableTemp(table,sql,null);
    }

    public void createIndex(String table, String index, String field)
    {
      String sql = "CREATE INDEX " + index + " ON " + table + "(" + field + ")";
      this.executeUpdate(sql,true);
    }

    public void createIndex(String table, String field)
    {
      createIndex(table, field, field);
    }

    public String createTableFrom(String table, String sql)
    {
      this.dropTable(table);
      String sql_ = "CREATE TABLE IF NOT EXISTS " + table + " " + sql;
      this.executeUpdate(sql_,true);
      return table;
    }

    public String createTableFrom(String table)
    {
      String tab_bak = table + "_bak";
      String sql = "SELECT * FROM " + table + " WHERE id = 0";
      return createTableFrom(tab_bak,sql);
    }

}
